package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import CONEXAO.Conexao;
import CORE.GenericDAO;
import DTO.AutorDTO;
import DTO.ObraDTO;
import DTO.UsuarioDTO;

public class ObraDAO extends GenericDAO<ObraDTO> {
	
	public ObraDAO() {
		super("obra", ObraDTO.class); 
	}

	public List<ObraDTO> pesquisarObrasDestaque(){
		try {
			List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT o.* ");
			sql.append("FROM comentario c, obra o ");
			sql.append("WHERE o.codigo = c.obra ");
			sql.append("GROUP BY c.obra ORDER BY AVG(c.rank) DESC LIMIT 8 ");              
            
            ResultSet rs = conn.prepareStatement(sql.toString()).executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();            
            
            conn.close();
            
            return obras;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<ObraDTO> pesquisarObrasMaisLidas(){
		try {
			List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT o.* ");
			sql.append("FROM lista l, obra o ");
			sql.append("WHERE o.codigo = l.obra ");
			sql.append("AND l.status = 1 ");
			sql.append("GROUP BY l.obra ORDER BY COUNT(l.obra) DESC LIMIT 8 ");              
            
            ResultSet rs = conn.prepareStatement(sql.toString()).executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();            
            
            conn.close();
            
            return obras;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<ObraDTO> pesquisarLivrosPorTitulo(ObraDTO obraDTO){
		try {
			List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE titulo LIKE ? ");
            sql.append(" AND obra IS NOT NULL ");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setString(1, "%"+obraDTO.getTitulo()+"%");
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();
            ps.close();
            
            conn.close();
            
            return obras;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
		
	public List<ObraDTO> pesquisarColecoesPorTitulo(ObraDTO obraDTO){
		try {
			List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE titulo LIKE ? ");
            sql.append(" AND obra IS NULL ");
            sql.append(" AND codigo != 1 ");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setString(1, "%"+obraDTO.getTitulo()+"%");
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();
            ps.close();
            
            conn.close();
            
            return obras;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean existe(ObraDTO obraDTO){
		try {
			ObraDTO obra = null;
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE titulo = ?");                        
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setString(1, obraDTO.getTitulo());
            
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
            	obra = criarObjeto(rs);
            }
                        
            rs.close();
            ps.close();
            
            conn.close();            
            return obra != null;
		} catch (Exception e) {			
			return false;
		}
	}
	
	public ObraDTO pesquisarPorTitulo(String titulo){
		try {
			ObraDTO obra = null;
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE titulo = ?");                        
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setString(1, titulo);
            
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
            	obra = criarObjeto(rs);
            }
                        
            rs.close();
            ps.close();
            
            conn.close();            
            return obra;
		} catch (Exception e) {			
			return null;
		}
	}
	
	public List<ObraDTO> pesquisarObrasDoAutor(AutorDTO autor){
		try {
			List<ObraDTO> obras = new ArrayList<ObraDTO>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT o.* FROM ");
            sql.append("autor a, autores aut, obra o ");
            sql.append("WHERE a.codigo = aut.autor ");
            sql.append("AND o.codigo = aut.obra ");
            sql.append("AND a.codigo = (?)");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, autor.getCodigo());
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();                        
            conn.close();
            
            return obras;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
			
	public List<ObraDTO> pesquisarLivrosDaColecao(ObraDTO obraDTO){
		try{
			List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE obra = (?) ");            
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, obraDTO.getCodigo());
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();
            ps.close();
            
            conn.close();
            
            return obras;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}					
	}
	
	public List<ObraDTO> naoPesquisarLivrosDaColecao(ObraDTO obraDTO){
		try{
			List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE obra != (?) ");
	        sql.append(" AND obra IS NOT NULL");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, obraDTO.getCodigo());
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();
            ps.close();
            
            conn.close();
            
            return obras;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}					
	}
		
	public List<ObraDTO> pesquisarTodosLivros() {
		try{            
            List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);  
            sql.append(" WHERE obra IS NOT NULL");
                        
            ResultSet rs = conn.prepareStatement(sql.toString()).executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();                        
            conn.close();
            
            return obras;
        } catch (Exception e) {            
            e.printStackTrace();   
            return null;
        }        
	}
	
	public List<ObraDTO> pesquisarTodasColecoes() {
		try{            
            List<ObraDTO> obras = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);  
            sql.append(" WHERE obra IS NULL ");
            sql.append(" AND codigo != 1");
                        
            ResultSet rs = conn.prepareStatement(sql.toString()).executeQuery();
            while(rs.next()){
            	obras.add(criarObjeto(rs));
            }
                        
            rs.close();                        
            conn.close();
            
            return obras;
        } catch (Exception e) {            
            e.printStackTrace();   
            return null;
        }        
	}	
	
	@Override
	public ObraDTO criarObjeto(ResultSet rs) throws SQLException {
		ObraDTO obra = new ObraDTO();
		obra.setCodigo(rs.getLong(1));
		obra.setDescricao(rs.getString(2));
		obra.setTitulo(rs.getString(3));
		obra.setPaginas(rs.getInt(4));
		obra.setEditora(rs.getString(5));
		obra.setEdicao(rs.getInt(6));
		obra.setCapa(rs.getBytes(7));
		obra.setAno(rs.getInt(8));		
		obra.setObra(pesquisarPorCodigo(rs.getLong(9)));
		return obra;
	}

	@Override
	public void setarPSExcluir(PreparedStatement ps, ObraDTO obra) throws SQLException {		
		ps.setLong(1, obra.getCodigo());
	}

	@Override
	public void setarPSAlterar(PreparedStatement ps, ObraDTO obra) throws SQLException {		
		setarPSIncluir(ps, obra);
		ps.setLong(9, obra.getCodigo());
	}

	@Override
	public void setarPSIncluir(PreparedStatement ps, ObraDTO obra) throws SQLException {					
		ps.setString(1, obra.getDescricao());
		ps.setString(2, obra.getTitulo());		
		ps.setString(3, obra.getEditora());
		ps.setInt(4, obra.getPaginas());		
		ps.setObject(5, obra.getEdicao());
		ps.setObject(6, obra.getAno());
		ps.setBytes(7, obra.getCapa());		
		ps.setObject(8, obra.getObra() != null ? obra.getObra().getCodigo() : null);				
	}	
}